package cn.dida.dao;

import cn.dida.model.Branch;
import cn.dida.model.Pageable;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * 网点管理 数据交互类
 * @author weiwei
 */
@Repository
public class BranchDao extends DaoBase {

	/**
	 * 网点列表
	 * @param companyId 公司id
	 * @param keyword 查询条件
	 * @param state 网点状态,1正常，2停用
	 * @param page 分页游标
	 * @return Pageable<Branch> 网点分页列表
	 * */
    public Pageable<Branch> query(Integer companyId, String keyword, Integer state, int page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from branch brc join staff stf on brc.create_staff_id = stf.id ");
        StringBuffer sqlQueryResult = new StringBuffer("select brc.*,stf.id 'createStaffId',stf.account 'createStaffAccount' from branch brc join staff stf on brc.create_staff_id = stf.id");

        StringBuffer sqlWhere = new StringBuffer(" where brc.company_id = ? ");
        args = ArrayUtils.add(args, companyId);
        if (state != null) {
            sqlWhere.append(" and brc.state = ?");
            args = ArrayUtils.add(args, state);
        }
        if (keyword != null) {
            sqlWhere.append(" and brc.name like ? or brc.link_phone = ?");
            args = ArrayUtils.add(args, "%" + keyword + "%");
            args = ArrayUtils.add(args, keyword);
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by create_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Branch> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Branch.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }

    /**
     * 网点列表（数据导出用）
     *
     * @param companyId 所属公司id
     * @param keyword   查询条件
     * @param state     网点状态：1正常，2停用
     * @return List<Branch> 网点列表
     * @date 2016-12-08 11:01
     */
    public List<Branch> queryForExport(Integer companyId, String keyword, Integer state) {
        Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select brc.*,stf.id 'createStaffId',stf.account 'createStaffAccount' from branch brc join staff stf on brc.create_staff_id = stf.id");

        StringBuffer sqlWhere = new StringBuffer(" where brc.company_id = ?");
        args = ArrayUtils.add(args, companyId);
        if (state != null) {
            sqlWhere.append(" and brc.state = ?");
            args = ArrayUtils.add(args, state);
        }
        if (keyword != null) {
            sqlWhere.append(" and brc.name like ? or brc.link_phone = ?");
            args = ArrayUtils.add(args, "%" + keyword + "%");
            args = ArrayUtils.add(args, keyword);
        }

        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by create_time desc ");
        List<Branch> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Branch.class));
        return result;
    }

    /**
     * 获取网点列表
     * @param companyId 公司id
     * @param state 网点状态：1正常，2停用
     * @return List<Branch> 网点列表数据
     * */
    public List<Branch> query(Integer companyId, Integer state) {
        String sql = "select brc.*,stf.id 'createStaffId',stf.account 'createStaffAccount' from branch brc join staff stf on brc.create_staff_id = stf.id where brc.company_id = ? and brc.state = ?";
        List<Branch> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Branch.class), companyId, state);
        return result;
    }

    /**
     * 新增网点
     * @param branch 网点实体信息
     * @return int 受影响的记录行数
     * */
    public int insert(Branch branch) {
        String sql = "insert into branch(company_id, name, link_phone, link_man, department_id, geo_point, address, remark, state, pictures, create_staff_id, create_time) values (:company_id, :name, :link_phone, :link_man, :department_id, :geo_point, :address, :remark, :state, :pictures, :create_staff_id, :create_time)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("company_id", branch.getCompanyId());
        paramMap.put("name", branch.getName());
        paramMap.put("link_phone", branch.getLinkPhone());
        paramMap.put("link_man", branch.getLinkMan());
        paramMap.put("department_id", branch.getDepartmentId());
        paramMap.put("geo_point", branch.getGeoPoint());
        paramMap.put("address", branch.getAddress());
        paramMap.put("remark", branch.getRemark());
        paramMap.put("pictures", branch.getPictures());
        paramMap.put("state", branch.getState());
        paramMap.put("create_staff_id", branch.getCreateStaffId());
        paramMap.put("create_time", new Date().getTime());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 更新网点
     * @param branch 网点实体信息
     * @return int 受影响的记录行数
     * */
    public int update(Branch branch) {
        String sql = "update branch set name=:name, link_phone=:link_phone, link_man=:link_man, department_id=:department_id, geo_point=:geo_point, address=:address, remark=:remark, pictures=:pictures, state=:state where company_id = :company_id and id = :id";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("name", branch.getName());
        paramMap.put("link_phone", branch.getLinkPhone());
        paramMap.put("link_man", branch.getLinkMan());
        paramMap.put("department_id", branch.getDepartmentId());
        paramMap.put("geo_point", branch.getGeoPoint());
        paramMap.put("address", branch.getAddress());
        paramMap.put("pictures", branch.getPictures());
        paramMap.put("remark", branch.getRemark());
        paramMap.put("state", branch.getState());
        paramMap.put("company_id", branch.getCompanyId());
        paramMap.put("id", branch.getId());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 根据公司id和网点id获取网点
     * @param companyId 公司id
     * @param id 网点id
     * @return Branch 网点
     * */
    public Branch get(Integer companyId, Integer id) {
        String sql = "select * from branch where company_id = ? and id = ?";
        try {
            Branch branch = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Branch.class), companyId, id);
            return branch;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据公司id和联系电话 查询网点
     * @param companyId 公司id
     * @param linkPhone 联系电话
     * @return Branch 网点
     * */
    public Branch getByLinkPhone(Integer companyId, String linkPhone) {
        String sql = "select * from branch where company_id = ? and link_phone = ?";
        try {
            Branch branch = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Branch.class), companyId, linkPhone);
            return branch;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 删除网点
     * @param companyId 公司id
     * @param id 网点id
     * @return int 受影响的记录行数
     * */
    public int delete(Integer companyId, Integer id) {
        String sql = "delete from branch where company_id = ? and id = ?";
        return jdbcTemplate.update(sql, companyId, id);
    }

    /**
     * 根据网点id,删除奖品与网点关联
     * @param branchId 网点id
     * @return int 受影响的记录行数
     * @date 2016-11-20 17:42
     */
    public int delPBByBranchId(String branchId) {
        String sql = "delete from prize_branch where branch_id = ?";
        return jdbcTemplate.update(sql, branchId);
    }
}
